package com.dy.dao;

import com.dy.bean.Sugar;
import com.dy.bean.SugarTwo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("SELECT sum(amount)'value' FROM order_info WHERE SUBSTRING(create_time,1,10) ='2023-01-06'")
    Double showDay();
    @Select("call sp1")
    List<Sugar> showXd();
    @Select("SELECT a.*,RANK() over(PARTITION by a.name ORDER BY a.distance)FROM\n" +
            "(SELECT b.org_name 'name',\n" +
            "sum(t.actual_distance)'distance',\n" +
            "count(*)'num',\n" +
            "sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))'time',\n" +
            "(sum(TIMESTAMPDIFF(SECOND,actual_start_time,actual_end_time))/count(*))'avgTime',\n" +
            "(sum(t.actual_distance)/count(*))'avgNum'\n" +
            "FROM transport_task t \n" +
            "LEFT JOIN line_base_info l on l.id = t.line_id\n" +
            "LEFT JOIN base_organ b on l.org_id = b.id\n" +
            "GROUP BY b.org_name) a")
    List<SugarTwo> showJg();
    @Select("SELECT r1.name'name',count(*) 'value' FROM transport_task_detail d \n" +
            "LEFT JOIN transport_task t on d.transport_task_id = t.id\n" +
            "LEFT JOIN line_base_info l on t.line_id = l.id\n" +
            "LEFT JOIN base_organ b on l.org_id=b.id\n" +
            "LEFT JOIN base_region_info r on b.region_id =r.id\n" +
            "LEFT JOIN base_region_info r1 on r.parent_id = r1.id\n" +
            "GROUP BY r1.name")
    List<Sugar> showMap();
}
